﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:SYS_EMP
	/// </summary>
	public partial class SYS_EMP : BaseDAL<GSLMS.Entity.TB_SYS_EMP>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public SYS_EMP()
		{}
		#region  Method
        /// <summary>
		///  增加一条数据
		/// </summary>
        public override bool Add(GSLMS.Entity.TB_SYS_EMP model)
        {
            bool flag = false;
            if (AddEmp(model) == 1)
                flag = true;
            return flag;
        }

        /// <summary>
        ///  修改一条数据
        /// </summary>
        public override bool Update(GSLMS.Entity.TB_SYS_EMP model)
        {
            bool flag = false;
            if (UpdateEmp(model) == 1)
                flag = true;
            return flag;
        }

		/// <summary>
		///  增加一条数据,-1用户名已经存在，1成功，0失败
		/// </summary>
        public  int AddEmp(GSLMS.Entity.TB_SYS_EMP model)
		{
            int num = 0;
			
            //密码设置
            if (model.LOGPWD.Trim() != "")
            {
                model.PWDRAND = OracleHelper.CreateSalt(6).ToUpper();
                model.LOGPWD = OracleHelper.MD5Encode(model.LOGPWD + model.PWDRAND);
            }
            else
            {
                model.PWDRAND = "";
            }

			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_EMPNO", model.EMPNO),
								new OracleParameter("P_EMPNAME", model.EMPNAME),
								new OracleParameter("P_IDCARD", model.IDCARD),
								new OracleParameter("P_GENDER", model.GENDER),
								new OracleParameter("P_BIRTHDAY", model.BIRTHDAY),
								new OracleParameter("P_DUTIES", model.DUTIES),
								new OracleParameter("P_ORGID", model.ORGID),
								new OracleParameter("P_TELCODE", model.TELCODE),
								new OracleParameter("P_TEL", model.TEL),
								new OracleParameter("P_MOBILE", model.MOBILE),
								new OracleParameter("P_HOMETELCODE", model.HOMETELCODE),
								new OracleParameter("P_HOMETEL", model.HOMETEL),
								new OracleParameter("P_OFFICETELCODE", model.OFFICETELCODE),
								new OracleParameter("P_OFFICETEL", model.OFFICETEL),
								new OracleParameter("P_OFFICETELSUB", model.OFFICETELSUB),
								new OracleParameter("P_HOMEADDR", model.HOMEADDR),
								new OracleParameter("P_POST", model.POST),
								new OracleParameter("P_EMAIL", model.EMAIL),
								new OracleParameter("P_ISSALESMAN", model.ISSALESMAN),
								new OracleParameter("P_ISHOLIDAYS", model.ISHOLIDAYS),
								new OracleParameter("P_ISLEAVE", model.ISLEAVE),
								new OracleParameter("P_LEAVEDATE", model.LEAVEDATE),
								new OracleParameter("P_ISPRINCIPAL", model.ISPRINCIPAL),
								new OracleParameter("P_REMARK", model.REMARK),
								new OracleParameter("P_ISUSER", model.ISUSER),
								new OracleParameter("P_USERNAME", model.USERNAME),
								new OracleParameter("P_LOGPWD", model.LOGPWD),
								new OracleParameter("P_PWDRAND", model.PWDRAND),
								new OracleParameter("P_USERTYPE", model.USERTYPE),
								new OracleParameter("P_LASTLOGINDATE", model.LASTLOGINDATE),
								new OracleParameter("P_USERSTATUS", model.USERSTATUS),
								new OracleParameter("P_CREATEBY", model.CREATEBY),
								new OracleParameter("P_CREATETIME", model.CREATETIME),
								new OracleParameter("P_MODIFYBY", model.MODIFYBY),
								new OracleParameter("P_MODIFYTIME", model.MODIFYTIME),
                                new OracleParameter("returnvalue",OracleType.Int32)
                            };
  
			for (int i = 0; i < 36; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}
            parms[36].Direction = ParameterDirection.Output;

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_emp_add", parms);
                if (parms[36].Value != DBNull.Value)
                    num = OracleHelper.ToInt(parms[36].Value.ToString());

                //清除缓存
                OracleHelper.RemoveCacheObject("_EmpInfoList");
               
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return num;
		}


		/// <summary>
        ///  更新一条数据,-1用户名已经存在，1成功，0失败
		/// </summary>
        public  int UpdateEmp(GSLMS.Entity.TB_SYS_EMP model)
		{
            int num = 0;
            //密码设置
            if (model.LOGPWD.Trim() != "")
            {
                model.PWDRAND = OracleHelper.CreateSalt(6).ToUpper();
                model.LOGPWD = OracleHelper.MD5Encode(model.LOGPWD + model.PWDRAND);
            }
            else
            {
                model.PWDRAND = "";
            }

			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_EMPNO", model.EMPNO),
								new OracleParameter("P_EMPNAME", model.EMPNAME),
								new OracleParameter("P_IDCARD", model.IDCARD),
								new OracleParameter("P_GENDER", model.GENDER),
								new OracleParameter("P_BIRTHDAY", model.BIRTHDAY),
								new OracleParameter("P_DUTIES", model.DUTIES),
								new OracleParameter("P_ORGID", model.ORGID),
								new OracleParameter("P_TELCODE", model.TELCODE),
								new OracleParameter("P_TEL", model.TEL),
								new OracleParameter("P_MOBILE", model.MOBILE),
								new OracleParameter("P_HOMETELCODE", model.HOMETELCODE),
								new OracleParameter("P_HOMETEL", model.HOMETEL),
								new OracleParameter("P_OFFICETELCODE", model.OFFICETELCODE),
								new OracleParameter("P_OFFICETEL", model.OFFICETEL),
								new OracleParameter("P_OFFICETELSUB", model.OFFICETELSUB),
								new OracleParameter("P_HOMEADDR", model.HOMEADDR),
								new OracleParameter("P_POST", model.POST),
								new OracleParameter("P_EMAIL", model.EMAIL),
								new OracleParameter("P_ISSALESMAN", model.ISSALESMAN),
								new OracleParameter("P_ISHOLIDAYS", model.ISHOLIDAYS),
								new OracleParameter("P_ISLEAVE", model.ISLEAVE),
								new OracleParameter("P_LEAVEDATE", model.LEAVEDATE),
								new OracleParameter("P_ISPRINCIPAL", model.ISPRINCIPAL),
								new OracleParameter("P_REMARK", model.REMARK),
								new OracleParameter("P_ISUSER", model.ISUSER),
								new OracleParameter("P_USERNAME", model.USERNAME),
								new OracleParameter("P_LOGPWD", model.LOGPWD),
								new OracleParameter("P_PWDRAND", model.PWDRAND),
								new OracleParameter("P_USERTYPE", model.USERTYPE),
								new OracleParameter("P_LASTLOGINDATE", model.LASTLOGINDATE),
								new OracleParameter("P_USERSTATUS", model.USERSTATUS),
								new OracleParameter("P_CREATEBY", model.CREATEBY),
								new OracleParameter("P_CREATETIME", model.CREATETIME),
								new OracleParameter("P_MODIFYBY", model.MODIFYBY),
								new OracleParameter("P_MODIFYTIME", model.MODIFYTIME),
                                new OracleParameter("returnvalue",OracleType.Int32)};
  
			for (int i = 0; i < 36; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}
            parms[36].Direction = ParameterDirection.Output;
			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_emp_update", parms);

                if (parms[36].Value != DBNull.Value)
                    num = OracleHelper.ToInt(parms[36].Value.ToString());
                //清除缓存
                OracleHelper.RemoveCacheObject("_EmpInfoList");
                
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return num;
		}

        
        /// <summary>
        /// 修改登录密码
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="pwd">原密码</param>
        /// <param name="newPwd">新密码</param>
        /// <returns>-1用户不存在,-2原密码输入不正确，1成功，0失败</returns>
        public int UpdatePwd(string userName, string pwd, string newPwd)
        {
            int num = 0;
            //验证原密码是否正确
            string logPwd = "";
            string pwdRand = "";
            GSLMS.Entity.TB_SYS_EMP emp = new GSLMS.Entity.TB_SYS_EMP();
            emp = GetByUserName(userName);
            if (emp == null || emp.USERNAME == "")
            {
                return -1;
            }

            logPwd = OracleHelper.MD5Encode(pwd + emp.PWDRAND);
            if (!emp.LOGPWD.Equals(logPwd))
            {
                return -2;
            }

            pwdRand = OracleHelper.CreateSalt(6).ToUpper();
            logPwd = OracleHelper.MD5Encode(newPwd + pwdRand);
            

            OracleParameter[] parms = new OracleParameter[] { 
             new OracleParameter("p_username",userName),
             new OracleParameter("p_newPwd",logPwd),
             new OracleParameter("p_pwdRand",pwdRand),
             new OracleParameter("returnvalue",OracleType.Int32)
           };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;
            parms[2].Direction = ParameterDirection.Input;
            parms[3].Direction = ParameterDirection.Output;
            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_emp_updatepwd", parms);
                if (parms[3].Value != DBNull.Value)
                {
                    num = OracleHelper.ToInt(parms[3].Value.ToString());
                    if (num == 1)
                    {
                        //清除缓存
                        OracleHelper.RemoveCacheObject("_EmpInfoList");
                    }
                }
            }
            catch (OracleException ex)
            {
                log.ExceptionHand(ex);
                num = 0;
            }
            return num;
        }

        /// <summary>
        /// 修改登录密码
        /// </summary>
        /// <param name="ID">职员记录ID</param>
        /// <param name="newPwd">MD5新密码</param>
        /// <returns>-1用户不存在,1成功，0失败</returns>
        public int ResetPwd(long ID, string newPwd)
        {
            int num = 0;
            //验证原密码是否正确
            string logPwd = "";
            string pwdRand = "";
            GSLMS.Entity.TB_SYS_EMP emp = new GSLMS.Entity.TB_SYS_EMP();
            emp =GetByID(ID);
            if (emp == null || emp.USERNAME == "")
            {
                return -1;
            }


            pwdRand = OracleHelper.CreateSalt(6).ToUpper();
            logPwd = OracleHelper.MD5Encode(newPwd + pwdRand);


            OracleParameter[] parms = new OracleParameter[] { 
             new OracleParameter("p_username",emp.USERNAME),
             new OracleParameter("p_newPwd",logPwd),
             new OracleParameter("p_pwdRand",pwdRand),
             new OracleParameter("returnvalue",OracleType.Int32)
           };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;
            parms[2].Direction = ParameterDirection.Input;
            parms[3].Direction = ParameterDirection.Output;
            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_emp_updatepwd", parms);
                if (parms[3].Value != DBNull.Value)
                {
                    num = OracleHelper.ToInt(parms[3].Value.ToString());
                    if (num == 1)
                    {
                        //清除缓存
                        OracleHelper.RemoveCacheObject("_EmpInfoList");
                    }
                }
            }
            catch (OracleException ex)
            {
                log.ExceptionHand(ex);
                num = 0;
            }
            return num;
        }

		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", ID)};
  
				parms[0].Direction = ParameterDirection.Input;

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_emp_delete", parms);
				flag = true;
                //清除缓存
                OracleHelper.RemoveCacheObject("_EmpInfoList");
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

        /// <summary>
        /// 解冻用户/冻结用户
        /// </summary>
        /// <param name="ID">职员记录ID</param>
        /// <param name="userStatus">用户状态，1解冻，0冻结</param>
        /// <returns></returns>
        public bool LockOrUnLockUser(long ID, int userStatus)
        {
            bool flag = false;
            string sql = "update tb_sys_emp set userstatus=" + userStatus.ToString() + " where id=" + ID.ToString();
            try
            {
                int num = OracleHelper.ExecuteNonQuery(sql);
                if (num > 0)
                {
                    flag = true;
                    //清除缓存
                    OracleHelper.RemoveCacheObject("_EmpInfoList");
                }

            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

            return flag;
        }

        /// <summary>
        /// 验证用户登录
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="pwd">MD5加密密码</param>
        /// <returns></returns>
        public GSLMS.Entity.TB_SYS_EMP CheckLogin(string userName, string pwd)
        {
            string logPwd = "";
            GSLMS.Entity.TB_SYS_EMP emp = new GSLMS.Entity.TB_SYS_EMP();
            emp = GetByUserName(userName);
            if (emp == null || emp.USERNAME == "")
            {
                emp = new GSLMS.Entity.TB_SYS_EMP();
                emp.IsLogin = false;
                return emp;
            }

            logPwd = OracleHelper.MD5Encode(pwd + emp.PWDRAND);
            if (emp.LOGPWD.Equals(logPwd))
            {
                emp.IsLogin = true;
                emp.LASTLOGINDATE = DateTime.Now;
                //UpdateLastLoginDate(ui.USERNAME);
            }

            return emp;
        }

        /// <summary>
        /// 更新最新登录时间
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns></returns>
        public bool UpdateLastLoginDate(string userName)
        {
            bool flag = false;
            string sql = "";
            sql = "update tb_sys_emp set LASTLOGINDATE=to_date('" + DateTime.Now.ToString() + "','yyyy-mm-dd hh24:mi:ss'),logintimes=logintimes+1 where username='" + OracleHelper.ReplaceUnsafeSql(userName) + "'";

            int num = OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.Text, sql);
            if (num > 0)
                flag = true;

            return flag;
        }

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_SYS_EMP DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["EMPNO"]!=null && dr["EMPNO"].ToString()!="")
				{
					model.EMPNO=dr["EMPNO"].ToString();
				}
				if(dr["EMPNAME"]!=null && dr["EMPNAME"].ToString()!="")
				{
					model.EMPNAME=dr["EMPNAME"].ToString();
				}
				if(dr["IDCARD"]!=null && dr["IDCARD"].ToString()!="")
				{
					model.IDCARD=dr["IDCARD"].ToString();
				}
				if(dr["GENDER"]!=null && dr["GENDER"].ToString()!="")
				{
					model.GENDER=dr["GENDER"].ToString();
				}
				if(dr["BIRTHDAY"]!=null && dr["BIRTHDAY"].ToString()!="")
				{
					model.BIRTHDAY=dr["BIRTHDAY"].ToString();
				}
				if(dr["DUTIES"]!=null && dr["DUTIES"].ToString()!="")
				{
					model.DUTIES=dr["DUTIES"].ToString();
				}
				if(dr["ORGID"]!=null && dr["ORGID"].ToString()!="")
				{
					model.ORGID=OracleHelper.ToLong(dr["ORGID"].ToString());
				}
				if(dr["TELCODE"]!=null && dr["TELCODE"].ToString()!="")
				{
					model.TELCODE=dr["TELCODE"].ToString();
				}
				if(dr["TEL"]!=null && dr["TEL"].ToString()!="")
				{
					model.TEL=dr["TEL"].ToString();
				}
				if(dr["MOBILE"]!=null && dr["MOBILE"].ToString()!="")
				{
					model.MOBILE=dr["MOBILE"].ToString();
				}
				if(dr["HOMETELCODE"]!=null && dr["HOMETELCODE"].ToString()!="")
				{
					model.HOMETELCODE=dr["HOMETELCODE"].ToString();
				}
				if(dr["HOMETEL"]!=null && dr["HOMETEL"].ToString()!="")
				{
					model.HOMETEL=dr["HOMETEL"].ToString();
				}
				if(dr["OFFICETELCODE"]!=null && dr["OFFICETELCODE"].ToString()!="")
				{
					model.OFFICETELCODE=dr["OFFICETELCODE"].ToString();
				}
				if(dr["OFFICETEL"]!=null && dr["OFFICETEL"].ToString()!="")
				{
					model.OFFICETEL=dr["OFFICETEL"].ToString();
				}
				if(dr["OFFICETELSUB"]!=null && dr["OFFICETELSUB"].ToString()!="")
				{
					model.OFFICETELSUB=dr["OFFICETELSUB"].ToString();
				}
				if(dr["HOMEADDR"]!=null && dr["HOMEADDR"].ToString()!="")
				{
					model.HOMEADDR=dr["HOMEADDR"].ToString();
				}
				if(dr["POST"]!=null && dr["POST"].ToString()!="")
				{
					model.POST=dr["POST"].ToString();
				}
				if(dr["EMAIL"]!=null && dr["EMAIL"].ToString()!="")
				{
					model.EMAIL=dr["EMAIL"].ToString();
				}
				if(dr["ISSALESMAN"]!=null && dr["ISSALESMAN"].ToString()!="")
				{
					model.ISSALESMAN=OracleHelper.ToInt(dr["ISSALESMAN"].ToString());
				}
				if(dr["ISHOLIDAYS"]!=null && dr["ISHOLIDAYS"].ToString()!="")
				{
                    model.ISHOLIDAYS = OracleHelper.ToInt(dr["ISHOLIDAYS"].ToString());
				}
				if(dr["ISLEAVE"]!=null && dr["ISLEAVE"].ToString()!="")
				{
                    model.ISLEAVE = OracleHelper.ToInt(dr["ISLEAVE"].ToString());
				}
				if(dr["LEAVEDATE"]!=null && dr["LEAVEDATE"].ToString()!="")
				{
					model.LEAVEDATE=DateTime.Parse(dr["LEAVEDATE"].ToString());
				}
				if(dr["ISPRINCIPAL"]!=null && dr["ISPRINCIPAL"].ToString()!="")
				{
                    model.ISPRINCIPAL = OracleHelper.ToInt(dr["ISPRINCIPAL"].ToString());
				}
				if(dr["REMARK"]!=null && dr["REMARK"].ToString()!="")
				{
					model.REMARK=dr["REMARK"].ToString();
				}
				if(dr["ISUSER"]!=null && dr["ISUSER"].ToString()!="")
				{
                    model.ISUSER = OracleHelper.ToInt(dr["ISUSER"].ToString());
				}
				if(dr["USERNAME"]!=null && dr["USERNAME"].ToString()!="")
				{
					model.USERNAME=dr["USERNAME"].ToString();
				}
				if(dr["LOGPWD"]!=null && dr["LOGPWD"].ToString()!="")
				{
					model.LOGPWD=dr["LOGPWD"].ToString();
				}
				if(dr["PWDRAND"]!=null && dr["PWDRAND"].ToString()!="")
				{
					model.PWDRAND=dr["PWDRAND"].ToString();
				}
				if(dr["USERTYPE"]!=null && dr["USERTYPE"].ToString()!="")
				{
                    model.USERTYPE = OracleHelper.ToInt(dr["USERTYPE"].ToString());
				}
				if(dr["LASTLOGINDATE"]!=null && dr["LASTLOGINDATE"].ToString()!="")
				{
					model.LASTLOGINDATE=DateTime.Parse(dr["LASTLOGINDATE"].ToString());
				}
				if(dr["USERSTATUS"]!=null && dr["USERSTATUS"].ToString()!="")
				{
                    model.USERSTATUS = OracleHelper.ToInt(dr["USERSTATUS"].ToString());
				}
				if(dr["CREATEBY"]!=null && dr["CREATEBY"].ToString()!="")
				{
					model.CREATEBY=dr["CREATEBY"].ToString();
				}
				if(dr["CREATETIME"]!=null && dr["CREATETIME"].ToString()!="")
				{
					model.CREATETIME=DateTime.Parse(dr["CREATETIME"].ToString());
				}
				if(dr["MODIFYBY"]!=null && dr["MODIFYBY"].ToString()!="")
				{
					model.MODIFYBY=dr["MODIFYBY"].ToString();
				}

                if (dr["OrgName"] != null && dr["OrgName"].ToString() != "")
                {
                    model.OrgName = dr["OrgName"].ToString();
                }

				if(dr["MODIFYTIME"]!=null && dr["MODIFYTIME"].ToString()!="")
				{
					model.MODIFYTIME=DateTime.Parse(dr["MODIFYTIME"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_SYS_EMP GetByID(long ID)
		{
			GSLMS.Entity.TB_SYS_EMP sModel = new GSLMS.Entity.TB_SYS_EMP();
            DataTable dt = null;

            dt = OracleHelper.DataTableFileter(GetAllByCache(), " id=" + ID.ToString(), "");
            if (dt != null && dt.Rows.Count > 0)
                sModel = DataRowToModel(dt.Rows[0]);
            else
                return null;

            //string sql = "";
            //sql = "select TB_SYS_EMP.*,(select orgname from tb_sys_org where id=TB_SYS_EMP.orgid) orgname from TB_SYS_EMP where id=" + ID.ToString();
            //DataSet ds = OracleHelper.ExecuteDataset(sql);
            //if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            //{
            //    sModel = DataRowToModel(ds.Tables[0].Rows[0]);
            //}
            //else
            //{
            //return null;
            //}
            return sModel;
		}

        /// <summary>
        /// 通过用户名获取用户信息
        /// </summary>
        public GSLMS.Entity.TB_SYS_EMP GetByUserName(string userName)
        {
            GSLMS.Entity.TB_SYS_EMP sModel = new GSLMS.Entity.TB_SYS_EMP();
            DataTable dt = null;

            dt = OracleHelper.DataTableFileter(GetAllByCache(), " isuser=1 and USERNAME<>'' and username='" + userName + "'", "");
            if (dt != null && dt.Rows.Count > 0)
                sModel = DataRowToModel(dt.Rows[0]);
            else
                return null;

            return sModel;
        }

        /// <summary>
        /// 提取所有业务员信息,包含已经离职、休假的业务员
        /// </summary>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetSalesAllList()
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISSALESMAN=1", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
            
            return list;
        }

        /// <summary>
        /// 提取所有职员信息
        /// </summary>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetAllEmp()
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), "", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }

            return list;
        }

        /// <summary>
        /// 提取所有未离职员信息
        /// </summary>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetEffAllEmp()
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISLEAVE=0", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }

            return list;
        }


        /// <summary>
        /// 提取所有有效业务员信息,不包含已经离职业务员
        /// </summary>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetSalesEffList()
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISSALESMAN=1 and ISLEAVE=0", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
           
            return list;
        }

        /// <summary>
        /// 提取所有用户信息,包含已经离职和休假的用户
        /// </summary>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetUserAllList()
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISUSER=1 and USERNAME<>''", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
           
            return list;
        }

        /// <summary>
        /// 提取所有用户信息,不包含已经离职用户
        /// </summary>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetUserEffList()
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISUSER=1  and USERNAME<>''  and ISLEAVE=0", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
            
            return list;
        }

        /// <summary>
        /// 提取所有用户信息,不包含已经离职、已经休假的用户
        /// </summary>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetUserWorkList()
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISUSER=1  and USERNAME<>''   and ISLEAVE=0 and ISHOLIDAYS=0", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
           
            return list;
        }

        /// <summary>
        /// 提取拥有某种权限的所有用户信息,包含已经离职、已经休假的用户
        /// </summary>
        /// <param name="modelID">模块记录ID，如：Employee等</param>
        /// <param name="operType">操作标识符，如：ADD,ADU等</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetUserPrivAllList(string modelID,string operType)
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISUSER=1  and USERNAME<>''  ", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }


            return list;
        }

        /// <summary>
        /// 提取拥有某种权限的所有用户信息,不包含已经离职的用户
        /// </summary>
        /// <param name="modelID">模块记录ID，如：Employee等</param>
        /// <param name="operType">操作标识符，如：ADD,ADU等</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetUserPrivEffList(string modelID, string operType)
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISUSER=1  and USERNAME<>''   and ISLEAVE=0", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
            

            return list;
        }

        /// <summary>
        /// 提取拥有某种权限的所有用户信息,不包含已经离职、已经休假的用户
        /// </summary>
        /// <param name="modelID">模块记录ID，如：Employee等</param>
        /// <param name="operType">操作标识符，如：ADD,ADU等</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetUserPrivWorkList(string modelID, string operType)
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            dt = OracleHelper.DataTableFileter(GetAllByCache(), " ISUSER=1  and USERNAME<>''   and ISLEAVE=0 and ISHOLIDAYS=0", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
           
            return list;
        }

        /// <summary>
        /// 提取有风控审批权限的用户信息
        /// </summary>
        /// <param name="actionBy">查询人或是分配人，会提取同等级及其下级有风控审批权限的用户信息</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_EMP> GetRiskAuditUserList(string actionBy)
        {
            var list = new List<GSLMS.Entity.TB_SYS_EMP>();
            DataTable dt = null;
            OracleParameter[] parms = new OracleParameter[] { 
                new OracleParameter("p_actionby",actionBy),
                new OracleParameter("p_cur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_assignuser_get", parms);

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];
            
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
                    model = DataRowToModel(dt.Rows[i]);

                    if (dt.Rows[i]["SurveyingCount"] != null && dt.Rows[i]["SurveyingCount"].ToString() != "")
                        model.SurveyingCount = OracleHelper.ToInt(dt.Rows[i]["SurveyingCount"].ToString());

                    if (dt.Rows[i]["LastAssignTime"] != null && dt.Rows[i]["LastAssignTime"].ToString() != "")
                        model.LastAssignTime = Convert.ToDateTime(dt.Rows[i]["LastAssignTime"].ToString());

                    list.Add(model);
                }
            }

            return list;
        }




        /// <summary>
        /// 从缓存中获取职员信息
        /// </summary>
        /// <returns></returns>
        private DataTable GetAllByCache()
        {
            DataTable dt = null;
            if (OracleHelper.GetCachecObject("_EmpInfoList") != null)
                dt = (DataTable)OracleHelper.GetCachecObject("_EmpInfoList");
            if (dt == null || dt.Rows.Count == 0)
            {
                string sql = "";
                sql = "select TB_SYS_EMP.*,(select orgname from tb_sys_org where id=TB_SYS_EMP.orgid) orgname from TB_SYS_EMP";
                DataSet ds = OracleHelper.ExecuteDataset(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    OracleHelper.AddCacheObject("_EmpInfoList", (object)dt,0);
                    //清除缓存
                    //OracleHelper.RemoveCacheObject("_EmpInfoList");
                }
               
            }

            return dt;
        }
             


		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_SYS_EMP> GetList(GSLMS.Entity.TB_SYS_EMP searchParam)
		{
			var list=new List<GSLMS.Entity.TB_SYS_EMP>();
			DataTable dt = null;
			string tblName = "TB_SYS_EMP";
			string sqlWhere="";
			string strSort="";
			int pageSize=20;
			int pageIndex=1;
            string strFields = "TB_SYS_EMP.*,(select orgname from tb_sys_org where id=TB_SYS_EMP.orgid) OrgName";
			int totalCount = 0;
			int countFlag = 1;

            
            //是否计算总数
            countFlag = (searchParam.Pagination.ReCount != null && searchParam.Pagination.ReCount) ? 1 : 0;
            pageSize = (searchParam.Pagination.PageSize != null && searchParam.Pagination.PageSize > 0) ? searchParam.Pagination.PageSize : 15;
            pageIndex = (searchParam.Pagination.CurrentIndex != null && searchParam.Pagination.CurrentIndex > 0) ? searchParam.Pagination.CurrentIndex : 1;

            //员工编号
            if (searchParam.EMPNO != null && searchParam.EMPNO.Trim() != "")
            {
                sqlWhere += " and EMPNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.EMPNO) + "%'";
            }

            //员工姓名
            if (searchParam.EMPNAME != null && searchParam.EMPNAME.Trim() != "")
            {
                sqlWhere += " and EMPNAME like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.EMPNAME) + "%'";
            }

            //所属组织机构
            if (searchParam.ORGID != null && searchParam.ORGID > 0)
            {
                sqlWhere += " and ORGID=" + searchParam.ORGID.ToString();
            }

            //是否休假
            if (searchParam.ISHOLIDAYS != null && searchParam.ISHOLIDAYS > -1)
            {
                sqlWhere += " and ISHOLIDAYS=" + searchParam.ISHOLIDAYS.ToString();
            }

            //是否离职
            if (searchParam.ISLEAVE != null && searchParam.ISLEAVE > -1)
            {
                sqlWhere += " and ISLEAVE=" + searchParam.ISLEAVE.ToString();
            }

            //是否用户
            if (searchParam.ISUSER != null && searchParam.ISUSER > -1)
            {
                sqlWhere += " and ISUSER=" + searchParam.ISUSER.ToString();
            }

            //用户名
            if (searchParam.USERNAME != null && searchParam.USERNAME.Trim() != "")
            {
                sqlWhere += " and USERNAME like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.USERNAME) + "%'";
            }

            //用户类型
            if (searchParam.USERTYPE != null && searchParam.USERTYPE > 0)
            {
                sqlWhere += " and USERTYPE=" + searchParam.USERTYPE.ToString();
            }

            //用户状态
            if (searchParam.USERSTATUS != null && searchParam.USERSTATUS > -1)
            {
                sqlWhere += " and USERSTATUS=" + searchParam.USERSTATUS.ToString();
            }

             

			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_SYS_EMP model = new GSLMS.Entity.TB_SYS_EMP();
					model = DataRowToModel(dt.Rows[i]);
					list.Add(model);
				}
			}
			
			return list;
		}


		#endregion  Method
	}
}

